﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for Application
/// </summary>
public class Application
{
	public Application()
	{
	}

    public int applicationId { set; get; }
    public string firstName { set; get; }
    public string lastName { set; get; }
    public string nric { set; get; }
    public string nationality { set; get; }
    public DateTime? dob{ set; get; }
    public string gender { set; get; }
    public string smoker { set; get; }
    public string maritalStatus { set; get; }
    public string mailingAddress { set; get; }
    public string postalCode { set; get; }
    public string mobileNo { set; get; }
    public string telNo { set; get; }
    public string email { set; get; }
    public string occupation { set; get; }
    public string jobDuties { set; get; }
    public string employer { set; get; }
    public decimal? noOfYears { set; get; }
    public string industry { set; get; }
    public decimal? annualIncomeCurrent { set; get; }
    public decimal? annualIncomePrevious { set; get; }
    public string otherIncomeSource { set; get; }
    public decimal? otherSourceIncome { set; get; }
    public string sourceOfWealth { set; get; }
    public string bankruptcy { set; get; }
    public string benefitType { set; get; }
    public decimal? basicSumInsured { set; get; }
    public decimal? plusRiderSumInsured { set; get; }
    public decimal? waiverRiderPremium { set; get; }
    public decimal? totalAnnualPremium { set; get; }
    public string premiumFrequency { set; get; }
    public string paymentMethod { set; get; }
    public string autoRebalancing { set; get; }
    public List<Fund> fundAllocation { set; get; }

    public int userId { set; get; }
    public int accountId { set; get; }
    public int staffId { set; get; }
    public DateTime issueDate { set; get; }
    public string filePath { set; get; }
    

    Certificate certificate = new Certificate();

    public string create()
    {
        string sql = "INSERT R_POLICY (POLICY_NO,USER_ID,ACCOUNTID, ISSUE_DATE, BENEFIT_TYPE, STAFF_ID, BASIC_SUM_INSURED, PLUS_RIDER_SUM_INSURED, WAIVER_RIDER_PREMIUM, TOTAL_ANNUAL_PREMIUM, PREMIUM_FREQUENCY, PAYMENT_METHOD, STATUS)" +
                      "VALUES ( " +
                      "@policyNo," +
                      "@userId," +
                      "@accountId," +
                      "@issueDate," +
                      "@benefitType," +
                      "@staffId," +
                      "@basicSumInsured," +
                      "@plusRiderSumInsured," +
                      "@waiverRiderPremium," +
                      "@totalAnnualPremium," +
                      "@premiumFrequency," +
                      "@paymentMethod," +
                      "@status" +
                      ")";


        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();
        var cmd = new SqlCommand(sql, conn);
        string policyNo = new AccountsManager().GenerateNo("POLICY");
        cmd.Parameters.AddWithValue("@policyNo", policyNo);
        cmd.Parameters.AddWithValue("@userId", userId);
        cmd.Parameters.AddWithValue("@accountId", accountId);
        cmd.Parameters.AddWithValue("@issueDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@benefitType", benefitType);
        cmd.Parameters.AddWithValue("@staffId", staffId);
        cmd.Parameters.AddWithValue("@basicSumInsured", basicSumInsured);
        cmd.Parameters.AddWithValue("@plusRiderSumInsured", (object)plusRiderSumInsured ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@waiverRiderPremium", (object)waiverRiderPremium ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@totalAnnualPremium", totalAnnualPremium);
        cmd.Parameters.AddWithValue("@premiumFrequency", premiumFrequency);
        cmd.Parameters.AddWithValue("@paymentMethod", paymentMethod);
        cmd.Parameters.AddWithValue("@status", "ACTIVE");

        cmd.ExecuteNonQuery();
        


        foreach (Fund fund in fundAllocation)
        {
            sql = "INSERT R_POLICY_FUND (POLICY_NO,FUND_CODE,ALLOCATION,INVEST_UNITS, INSURANCE_CHARGE_UNITS, PLUS_RIDER_CHARGE_UNITS, OTHER_CHARGES_UNITS, LAST_TRADE_DATE, STATUS)" +
                     "VALUES ( " +
                     "@policyNo," +
                     "@fundCode," +
                     "@allocation," +
                     "@investUnits," +
                     "@insuranceChargeUnits," +
                     "@plusRiderChargeUnits," +
                     "@otherChargesUnits," +
                     "@lastTradeDate," +
                     "@status" +
                     ")";
            cmd = new SqlCommand(sql, conn);

            decimal zero = 0m;
            cmd.Parameters.AddWithValue("@policyNo", policyNo);
            cmd.Parameters.AddWithValue("@fundCode", fund.code);
            cmd.Parameters.AddWithValue("@allocation", fund.allocation);
            cmd.Parameters.AddWithValue("@investUnits", zero);
            cmd.Parameters.AddWithValue("@insuranceChargeUnits", zero);
            cmd.Parameters.AddWithValue("@plusRiderChargeUnits", zero);
            cmd.Parameters.AddWithValue("@otherChargesUnits", zero);
            cmd.Parameters.AddWithValue("@lastTradeDate", DateTime.Now);
            cmd.Parameters.AddWithValue("@status", "ACTIVE");

            cmd.ExecuteNonQuery();
        }

            cmd = new SqlCommand(sql, conn);

            cmd.CommandText = "INSERT R_DOCUMENT (POLICY_NO,DOC_TYPE,DOC_PATH,UPLOAD_TIME)" +
                     "VALUES ( " +
                     "@policyNo," +
                     "@docType," +
                     "@docPath," +
                     "@uploadTime" +
                     ")";

        cmd.Parameters.AddWithValue("@policyNo", policyNo);
        cmd.Parameters.AddWithValue("@docType", "APPLICATION");
        cmd.Parameters.AddWithValue("@docPath", filePath);
        cmd.Parameters.AddWithValue("@uploadTime", DateTime.Now);

        cmd.ExecuteNonQuery();


        cmd = new SqlCommand(sql, conn);

        cmd.CommandText = "UPDATE CUSTOMER "
                 + "SET firstName=@firstName"
                 + ",lastName=@lastName"
                 + ",nric=@nric"
                 + ",nationality=@nationality"
                 + ",dob=@dob"
                 + ",gender=@gender"
                 + ",smoker=@smoker"
                 + ",mailingAddress=@mailingAddress"
                 + ",postalCode=@postalCode"
                 + ",mobileNo=@mobileNo"
                 + ",telNo=@telNo"
                 + ",email=@email"
                 + ",occupation=@occupation"
                 + ",jobDuties=@jobDuties"
                 + ",employer=@employer"
                 + ",noOfYears=@noOfYears"
                 + ",industry=@industry"
                 + ",annualIncomeCurrent=@annualIncomeCurrent"
                 + ",annualIncomePrevious=@annualIncomePrevious"
                 + ",otherIncomeSource=@otherIncomeSource"
                 + ",otherSourceIncome=@otherSourceIncome"
                 + ",sourceOfWealth=@sourceOfWealth"
                 + ",bankruptcy=@bankruptcy"      
                 + " WHERE nric = @nric";

        cmd.Parameters.AddWithValue("@firstName", firstName);
        cmd.Parameters.AddWithValue("@lastName", lastName);
        cmd.Parameters.AddWithValue("@nric", nric);
        cmd.Parameters.AddWithValue("@nationality", nationality);
        cmd.Parameters.AddWithValue("@dob", (object)dob ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@gender", gender);
        cmd.Parameters.AddWithValue("@smoker", smoker);
        cmd.Parameters.AddWithValue("@mailingAddress", mailingAddress);
        cmd.Parameters.AddWithValue("@postalCode", postalCode);
        cmd.Parameters.AddWithValue("@mobileNo", mobileNo);
        cmd.Parameters.AddWithValue("@telNo", telNo);
        cmd.Parameters.AddWithValue("@email", email);
        cmd.Parameters.AddWithValue("@occupation", occupation);
        cmd.Parameters.AddWithValue("@jobDuties", jobDuties);
        cmd.Parameters.AddWithValue("@employer", employer);
        cmd.Parameters.AddWithValue("@noOfYears", (object)noOfYears ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@industry", industry);
        cmd.Parameters.AddWithValue("@annualIncomeCurrent", (object)annualIncomeCurrent ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@annualIncomePrevious", (object)annualIncomePrevious ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@otherIncomeSource", otherIncomeSource);
        cmd.Parameters.AddWithValue("@otherSourceIncome", (object)otherSourceIncome ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@sourceOfWealth", sourceOfWealth);
        cmd.Parameters.AddWithValue("@bankruptcy", bankruptcy);

        cmd.ExecuteNonQuery();

        conn.Close();


        PolicyRules policyRules = new PolicyRules();
        policyRules.payPremium(accountId,policyNo);


        return policyNo;

    }



    /*
    public int save()
    {
        string sql = "INSERT INTO R_APPLICATION"
                 + "(first_name"
                 + ",last_name"
                 + ",nric"
                 + ",nationality"
                 + ",dob"
                 + ",gender"
                 + ",smoker"
                 + ",mailing_address"
                 + ",postal_code"
                 + ",mobile_no"
                 + ",tel_no"
                 + ",email"
                 + ",occupation"
                 + ",job_duties"
                 + ",employer"
                 + ",no_of_years"
                 + ",industry"
                 + ",annual_income_current"
                 + ",annual_income_previous"
                 + ",other_income_source"
                 + ",other_source_income"
                 + ",source_of_wealth"
                 + ",bankruptcy"
                 + ",benefit_type"
                 + ",basic_sum_insured"
                 + ",plus_rider_sum_insured"
                 + ",waiver_rider_premium"
                 + ",total_premium"
                 + ",premium_frequency"
                 + ",payment_method"
                 + ",auto_rebalancing)"
                 + "VALUES"
                 + "(@first_name"
                 + ",@last_name"
                 + ",@nric"
                 + ",@nationality"
                 + ",@dob"
                 + ",@gender"
                 + ",@smoker"
                 + ",@mailing_address"
                 + ",@postal_code"
                 + ",@mobile_no"
                 + ",@tel_no"
                 + ",@email"
                 + ",@occupation"
                 + ",@job_duties"
                 + ",@employer"
                 + ",@no_of_years"
                 + ",@industry"
                 + ",@annual_income_current"
                 + ",@annual_income_previous"
                 + ",@other_income_source"
                 + ",@other_source_income"
                 + ",@source_of_wealth"
                 + ",@bankruptcy"
                 + ",@benefit_type"
                 + ",@basic_sum_insured"
                 + ",@plus_rider_sum_insured"
                 + ",@waiver_rider_premium"
                 + ",@total_premium"
                 + ",@premium_frequency"
                 + ",@payment_method"
                 + ",@auto_rebalancing)SELECT SCOPE_IDENTITY()";

       //"INSERT INTO R_APPLICATION(first_name,last_name,nric,nationality,dob,gender,smoker,mailing_address,postal_code,mobile_no,tel_no,email,occupation,job_duties,employer,no_of_years,industry,annual_income_current,annual_income_previous,other_income_source,other_source_income,source_of_wealth,bankruptcy,plan_name,plan_term_duration,basic_sum_insured,rider_name,rider_term_duration,rider_sum_insured,total_premium,auto_rebalancing,fund_1,fund_2,fund_3,fund_4,fund_5,fund_6,fund_7,fund_8,fund_9,fund_10,allocation_1,allocation_2,allocation_3,allocation_4,allocation_5,allocation_6,allocation_7,allocation_8,allocation_9,allocation_10)VALUES(@first_name,,@last_name,,@nric,,@nationality,,@dob, date,,@gender,,@smoker,,@mailing_address,,@postal_code,,@mobile_no,,@tel_no,,@email,,@occupation,,@job_duties,,@employer,,@no_of_years,,@industry,,@annual_income_current,,@annual_income_previous,,@other_income_source,,@other_source_income,,@source_of_wealth,,@bankruptcy,,@plan_name,,@plan_term_duration,,@basic_sum_insured,,@rider_name,,@rider_term_duration,,@rider_sum_insured,,@total_premium,,@auto_rebalancing,,@fund_1,,@fund_2,,@fund_3,,@fund_4,,@fund_5,,@fund_6,,@fund_7,,@fund_8,,@fund_9,,@fund_10,,@allocation_1,,@allocation_2,,@allocation_3,,@allocation_4,,@allocation_5,,@allocation_6,,@allocation_7,,@allocation_8,,@allocation_9,,@allocation_10)"
       Console.WriteLine(sql);
       SqlConnection conn = DBManager.getSqlConnection();
       conn.Open();
       var cmd = new SqlCommand(sql, conn);
  
        cmd.Parameters.Add(new SqlParameter("@first_name", (object)firstName ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@last_name", (object)lastName ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@nric", (object)nric ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@nationality", (object)nationality ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@dob", (object)dob ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@gender", (object)gender ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@smoker", (object)smoker ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@mailing_address", (object)mailingAddress ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@postal_code", (object)postalCode ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@mobile_no", (object)mobileNo ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@tel_no", (object)telNo ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@email", (object)email ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@occupation", (object)occupation ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@job_duties", (object)jobDuties ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@employer", (object)employer ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@no_of_years", (object)noOfYears ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@industry", (object)industry ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@annual_income_current", (object)annualIncomeCurrent ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@annual_income_previous", (object)annualIncomePrevious ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@other_income_source", (object)otherIncomeSource ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@other_source_income", (object)otherSourceIncome ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@source_of_wealth", (object)sourceOfWealth ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@bankruptcy", (object)bankruptcy ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@benefit_type", (object)benefitType ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@basic_sum_insured", (object)basicSumInsured ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@plus_rider_sum_insured", (object)plusRiderSumInsured ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@waiver_rider_premium", (object)waiverRiderPremium ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@total_premium", (object)totalPremium ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@premium_frequency", (object)premiumFrequency ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@payment_method", (object)paymentMethod ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@auto_rebalancing", (object)autoRebalancing ?? DBNull.Value));


        //cmd.ExecuteNonQuery();
        object newId = cmd.ExecuteScalar();
        int newIdInt = int.Parse(((Decimal)newId).ToString());

        this.applicationId = newIdInt;




        foreach (String fundCode in fundAllocation.Keys)
        {
            sql = "INSERT INTO R_APPLICATION_FUND"
                + ",application_id"
                + ",fund_code"
                + ",fund_allocation"
                + ",remark)"
                + "VALUES"
                + "(@application_id"
                 + ",@fund_code"
                + ",@fund_allocation"
                + ",@remark)";
        }

        cmd.Parameters.Add(new SqlParameter("@application_id", (object)newIdInt ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@fund_code", (object)fundAllocation["fundCode"] ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@fund_allocation", (object)fundAllocation["fundAllocation"] ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@remark", (object)fundAllocation["remark"] ?? DBNull.Value));
        

 
       conn.Close();
       return newIdInt; 

    }
    */



   

}